package com.unionx.unionxuf.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import com.unionx.unionxuf.entity.ConnectDBName;

public class DynamicConnect {

	public static Object getConnectionInfo(HttpServletRequest request) {

		HttpSession session =request.getSession();
		String cpid =  (String) session.getAttribute("cpid");
		ConnectDBName cdb =  new DynamicConnect().getDatasourceName(cpid);
		
		
		DynamicdataSource dynamicdataSource = DynamicdataSourceCreator.getInstance();
		dynamicdataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		dynamicdataSource.setUrl("jdbc:sqlserver://localhost:1433;");
		dynamicdataSource.setDatasourceName("ufdata_"+cdb.getCid()+"_"+cdb.getIbyear()+"");
		dynamicdataSource.setUsername("sa");
		dynamicdataSource.setPassword("root");
		Connection conn = null;
		try {
			Class.forName(dynamicdataSource.getDriverClassName());
			conn = DriverManager.getConnection(
					dynamicdataSource.getUrl() + "DatabaseName=" + dynamicdataSource.getDatasourceName(),
					dynamicdataSource.getUsername(), dynamicdataSource.getPassword());
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return "500";
		}

	}
	
	
	public static Object getConnection() {

		
		DynamicdataSource dynamicdataSource = DynamicdataSourceCreator.getInstance();
		dynamicdataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		dynamicdataSource.setUrl("jdbc:sqlserver://localhost:1433;");
		dynamicdataSource.setDatasourceName("UFSystem");
		dynamicdataSource.setUsername("sa");
		dynamicdataSource.setPassword("root");
		Connection conn = null;
		try {
			Class.forName(dynamicdataSource.getDriverClassName());
			conn = DriverManager.getConnection(
					dynamicdataSource.getUrl() + "DatabaseName=" + dynamicdataSource.getDatasourceName(),
					dynamicdataSource.getUsername(), dynamicdataSource.getPassword());
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return "500";
		}

	}
	
	
	
	public ConnectDBName getDatasourceName(String cpid){
		Statement stmt = null;
		Connection conn = null;
		ConnectDBName cdb = new ConnectDBName();
		try {
			conn = (Connection) DynamicConnect.getConnection();
			stmt = conn.createStatement();
			String sql = "SELECT cAcc_Id, MAX(iBeginYear) AS nf FROM UA_AccountDatabase group by cacc_id having cacc_id='"+cpid+"'";
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				cdb.setCid(rs.getString(1));
				cdb.setIbyear(rs.getString(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cdb;
	}

}
